package com.idrive.database;

import android.database.sqlite.SQLiteDatabase;

public class DatabaseUtility {

	public static final String SETTINGS_OPTION_SPEAKER_PHONE = "1";
	public static final String SETTINGS_OPTION_SMS = "2";
	public static final String SETTINGS_OPTION_VOICE_MESSAGE = "3";
	public static final String SETTINGS_OPTION_REJECT_CALL = "4";

	public static final String SMS_READER_OPTION_INACTIVE = "0";
	public static final String SMS_READER_OPTION_ALL_SMS = "1";
	public static final String SMS_READER_OPTION_PHONEBOOK = "2";

	// Database table
	public static final String TABLE_COMMON_DATA = "common_data";
	public static final String COLUMN_COMMON_DATA_OPTION = "option";
	public static final String COLUMN_COMMON_DATA_SMS = "sms";
	public static final String COLUMN_COMMON_DATA_READ_SMS = "read_sms";
	public static final String COLUMN_COMMON_DATA_TRACK_PATH = "track_path";

	// Database table
	public static final String TABLE_CALL_LOG = "call_log";
	public static final String COLUMN_CALL_LOG_ID = "id";
	public static final String COLUMN_CALL_LOG_TIME = "time";
	public static final String COLUMN_CALL_LOG_TELEPHONE = "telephone";
	public static final String COLUMN_CALL_LOG_EVENT = "event";

	// Database table
	public static final String TABLE_LOCATION = "location";
	public static final String COLUMN_LOCATION_TIME = "time";
	public static final String COLUMN_LOCATION_LATITUDE = "latitude";
	public static final String COLUMN_LOCATION_LONGITUDE = "longitude";
	public static final String COLUMN_LOCATION_GROUP_ID = "group_id";

	public static final String TRIGGER_CALL_LOG = "fifty_rows";

	// Database creation SQL statement
	private static final String CREATE_TABLE_COMMON_DATA = "create table " + TABLE_COMMON_DATA + " ("
	        + COLUMN_COMMON_DATA_OPTION + " text, " + COLUMN_COMMON_DATA_SMS + " text, " + COLUMN_COMMON_DATA_READ_SMS
	        + " text, " + COLUMN_COMMON_DATA_TRACK_PATH + " text);";

	// Database creation SQL statement
	private static final String CREATE_TABLE_CALL_LOG = "create table " + TABLE_CALL_LOG + " (" + COLUMN_CALL_LOG_ID
	        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_CALL_LOG_TIME + " text, " + COLUMN_CALL_LOG_TELEPHONE
	        + " text, " + COLUMN_CALL_LOG_EVENT + " text);";

	// Database creation SQL statement
	private static final String CREATE_TABLE_LOCATION = "create table " + TABLE_LOCATION + " (" + COLUMN_LOCATION_TIME
	        + " integer, " + COLUMN_LOCATION_LATITUDE + " real, " + COLUMN_LOCATION_LONGITUDE + " real, "
	        + COLUMN_LOCATION_GROUP_ID + "text);";

	private static final String CREATE_TRIGGER_CALL_LOG = "CREATE TRIGGER " + TRIGGER_CALL_LOG + " AFTER INSERT ON "
	        + TABLE_CALL_LOG + " BEGIN DELETE FROM " + TABLE_CALL_LOG + " WHERE " + COLUMN_CALL_LOG_ID + " IN (SELECT "
	        + COLUMN_CALL_LOG_ID + " FROM " + TABLE_CALL_LOG + " ORDER BY " + COLUMN_CALL_LOG_ID
	        + " DESC LIMIT 50, -1);END;";

	public static void onCreate(SQLiteDatabase database) {
		database.execSQL(CREATE_TABLE_COMMON_DATA);
		database.execSQL(CREATE_TABLE_CALL_LOG);
		database.execSQL(CREATE_TRIGGER_CALL_LOG);
		database.execSQL(CREATE_TABLE_LOCATION);
		database.execSQL("INSERT INTO " + TABLE_COMMON_DATA + " (" + COLUMN_COMMON_DATA_OPTION + ","
		        + COLUMN_COMMON_DATA_SMS + "," + COLUMN_COMMON_DATA_READ_SMS + "," + COLUMN_COMMON_DATA_TRACK_PATH
		        + ") VALUES('1','I am driving now. Please call me later','0','0');");
	}

	public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMON_DATA);
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_CALL_LOG);
		database.execSQL("DROP TRIGGER IF EXISTS " + TRIGGER_CALL_LOG);
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_LOCATION);
		onCreate(database);
	}
}
